<?php

require_once 'companyTicker.php';
require_once dirname(__FILE__)."/../db/db.php";

define("TBLTICKER_NAME", "tblTicker");

define("TBLTICKER_FLD_COMPANY", "CompanyName");
define("TBLTICKER_FLD_EXCHANGE", "StockExchange");
define("TBLTICKER_FLD_ID", "Id");


class tblTicker extends dbTable{
	public function __construct(){
		$keyValue = array(TBLTICKER_FLD_EXCHANGE, TBLTICKER_FLD_COMPANY);
		
		parent::__construct(TBLTICKER_NAME, $keyValue);
	}
	
	public function CreateTable(){
		$sql = "CREATE TABLE ".TBLTICKER_NAME." ("
				.TBLTICKER_FLD_COMPANY." VARCHAR(15) NOT NULL, "
				.TBLTICKER_FLD_EXCHANGE." VARCHAR(10) NOT NULL, "
				.TBLTICKER_FLD_ID." INT NOT NULL AUTO_INCREMENT, "
				."PRIMARY KEY(".TBLTICKER_FLD_ID."))";
		
		return $this->db->query($sql);
	}
	
	protected function record2object($recArrAssoc){
		$tickerList = array();
		foreach ($recArrAssoc as $field => $value){
			if($field != TBLTICKER_FLD_COMPANY && $field != TBLTICKER_FLD_EXCHANGE && $field != TBLTICKER_FLD_ID)
				$tickerList[$field] = $value;
		}
		return new companyTicker($recArrAssoc[TBLTICKER_FLD_COMPANY], $recArrAssoc[TBLTICKER_FLD_EXCHANGE], $tickerList);
	}

	protected function object2record($objCoTicker){
		$recAssocArray = array();
		$recAssocArray[TBLTICKER_FLD_COMPANY] = $objCoTicker->companyName;
		$recAssocArray[TBLTICKER_FLD_EXCHANGE] = $objCoTicker->exchange;
		
		if(isset($objCoTicker->tickerCode))
			foreach ($objCoTicker->tickerCode as $provName => $valTicker)
				$recAssocArray[$provName] = $valTicker;
		
		return $recAssocArray;
	}
	
	private function GetObjectFromQuery($qString){
		$qResult = $this->db->query($qString);
		if(isset($qResult)){
			$qArrAssoc = $this->db->getAssocArray($qResult);
			if(isset($qArrAssoc))
				return $this->record2object($qArrAssoc);
		}
		return NULL;
	}
	
	public function AddColumn($provName){
		$sql = "ALTER TABLE ".TBLTICKER_NAME." ADD `$provName` VARCHAR(15)";
		//ALTER TABLE `tblticker` ADD `finance.google.com` VARCHAR(15) NOT NULL 
		$this->db->query($sql);
	}
	
	public function UpdateTicker(companyTicker $objCoTicker){
		$provTickerSql = "";
		foreach ($objCoTicker->tickerCode as $provName => $tickerVal)
			$provTickerSql .= "`$provName` = \"$tickerVal\", ";
		$sql = "UPDATE ".TBLTICKER_NAME." SET $provTickerSql ".TBLTICKER_FLD_EXCHANGE." = \"$objCoTicker->exchange\" WHERE `".TBLTICKER_FLD_COMPANY."` = \"$objCoTicker->companyName\"";
		
		$this->db->query($sql);
	}
	
	public function InsertTicker(companyTicker $cTickerObj, $update = TRUE){
		if($update){
			if(NULL !== $this->GetCompanyByName($cTickerObj->GetName(), $cTickerObj->GetExchange()))
				return $this->UpdateTicker($cTickerObj);
		}
		$recordTicker = $this->object2record($cTickerObj);
		
		parent::Insert($recordTicker);
	}

	public function GetCompanyById($id){
		$sql = "SELECT * FROM ".TBLTICKER_NAME." WHERE ".TBLTICKER_FLD_ID." = $id";
		
		return $this->GetObjectFromQuery($sql);
	}
	
	public function GetCompanyByName($name, $xchg){
		$sql = "SELECT * FROM ".TBLTICKER_NAME." WHERE ".TBLTICKER_FLD_COMPANY." = \"$name\" AND ".TBLTICKER_FLD_EXCHANGE." = \"$xchg\"";
		
		return $this->GetObjectFromQuery($sql);
	}
	
	/**
	 * ritorna l'item successivo a quello con il nome $name
	 * @param $name nome dell'item 
	 * @return item successivo 
	 */
	public function GetNextCompanyByName($name, $xchg){
		$sql = "SELECT * FROM ".TBLTICKER_NAME." WHERE ".TBLTICKER_FLD_COMPANY." = \"$name\" AND ".TBLTICKER_FLD_EXCHANGE." = \"$xchg\"";
		$qResult = $this->db->query($sql);
		if(isset($qResult)){
			$rec = $this->db->getAssocArray($qResult);
			$sql = "SELECT * FROM ".TBLTICKER_NAME." WHERE ".TBLTICKER_FLD_ID." > ".$rec[TBLTICKER_FLD_ID]." ORDER BY ".TBLTICKER_FLD_ID." ASC LIMIT 1";
			return $this->GetObjectFromQuery($sql);
		}
		
		return NULL;
	}
	
	/**
	 * ritorna il primo item della tabella tblTicker
	 * @return item companyTicker
	 */
	public function GetFirstCompany(){
		$sql = "SELECT * FROM ".TBLTICKER_NAME." WHERE 1=1 ORDER BY ".TBLTICKER_FLD_ID." ASC LIMIT 1";
		
		return $this->GetObjectFromQuery($sql);
	}
}

class tblTickerTest{
	private $testTbl;
	
	public function __construct(){
		$this->testTbl = new tblTicker();
	}
	
	public function Test(){
		$ex1 = new companyTicker("Apple Inc.", "NASDAQ");
		$this->testTbl->InsertTicker($ex1);
		
		$this->testTbl->AddColumn("finance.google.com");
		
		$cod = array();
		$cod["finance.google.com"] = "MMM";
		$ex2 = new companyTicker("3M Inc.", "NYSE", $cod);
		$this->testTbl->InsertTicker($ex2);

		$this->testTbl->AddColumn("hopey.netfonds.com");
		
		$ex3 = new companyTicker("ENI SpA", "BIT");
		$this->testTbl->InsertTicker($ex3);
		
		$ex3->SetTicker("finance.google.com", "vENI");
		$ex3->SetTicker("hopey.netfonds.com", "vENIX");
		$this->testTbl->InsertTicker($ex3);
		
		$this->testTbl->Show();

		echo "<table border=1>";
		for($id = 1; $id <= 3; $id++){
			$co = $this->testTbl->GetCompanyById($id);
			$coName = $co->GetName();
			$coTickerGoogle = $co->GetTicker("finance.google.com");
			echo "<tr><td><b>$id</b></td><td>$coName</td><td>$coTickerGoogle</td></tr>";
		}
		echo "</table><br>";
		
		$newCo = $this->testTbl->GetCompanyByName($co->GetName(), $co->GetExchange());
		echo "<br>Last company is: ".$newCo->GetName();
		
		$newCo = $this->testTbl->GetFirstCompany();
		echo "<br>First company is: ".$newCo->GetName();
		
		$newCo = $this->testTbl->GetNextCompanyByName($newCo->GetName(), $newCo->GetExchange());
		echo "<br>next one is: ".$newCo->GetName();
		$newCo = $this->testTbl->GetNextCompanyByName($newCo->GetName(), $newCo->GetExchange());
		echo "<br>next one is: ".$newCo->GetName();
		$newCo = $this->testTbl->GetNextCompanyByName($newCo->GetName(), $newCo->GetExchange());
		echo "<br>next one is: ".$newCo->GetName();
		$newCo = $this->testTbl->GetNextCompanyByName($newCo->GetName(), $newCo->GetExchange());
		echo "<br>next one is: ".$newCo->GetName();
	}
}

?>
